package com.example.shujujiexi.xlsjiexi;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class QueryExcel {



    private  static String province="北京";
    public static void main(String[] args) {

        // 读取Excel文件
        File file = new File("C:\\Users\\74095\\Desktop\\新建文件夹 (6)\\test1\\1.xls");
        try {
            //得到所有数据
            List<List<String>> allData = readExcel(file);

            //直接将它写到excel中
            List<List<String>> result = dealData(allData);
            makeExcel(result);

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 获取数据
     *
     * @param file
     * @return
     * @throws Exception
     */
    private static List<List<String>> readExcel(File file) throws Exception {

        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(file.getAbsolutePath());
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(is);
        // 只有一个sheet,直接处理
        //创建一个Sheet对象
        Sheet sheet = wb.getSheet(0);
        // 得到所有的行数
        int rows = sheet.getRows();
        // 所有的数据
        List<List<String>> allData = new ArrayList<>();
        // 越过第一行 它是列名称
        for (int j = 1; j < rows; j++) {

            List<String> oneData = new ArrayList<String>();
            // 得到每一行的单元格的数据
            Cell[] cells = sheet.getRow(j);
            for (int k = 0; k < cells.length; k++) {

                oneData.add(cells[k].getContents().trim());
            }
            // 存储每一条数据
            allData.add(oneData);
            // 打印出每一条数据
            //System.out.println(oneData);

        }
        return allData;

    }

    /**
     * 处理数据
     */
    public static List<List<String>> dealData(List<List<String>> allData) {

        //结果
        List<List<String>> result = new ArrayList<List<String>>();

        for (int i = 0; i < allData.size(); i++) {

            List<String> oneDatai = allData.get(i);


            //判断是否为病例期
            if (province.equals(oneDatai.get(1).trim())) {


                List<String> oneDataj = allData.get(i);
                System.out.println(oneDataj);
                result.add(oneDataj);


            }
        }
        return result;
    }

    /**
     * 将数据写入到excel中
     */
    public static void makeExcel(List<List<String>> result) {

        //第一步，创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第二部，在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet(province);
        //第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，设置表头
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("CYCLE_ID");
        cell = row.createCell(1);
        cell.setCellValue("PROVINCE_NAME");
        cell = row.createCell(2);
        cell.setCellValue("EPARCHY_NAME");
        cell = row.createCell(3);
        cell.setCellValue("RELATION_TYPE_NAME");
        cell = row.createCell(4);
        cell.setCellValue("USER_ID");
        cell = row.createCell(5);
        cell.setCellValue("PUD_FEE");
        cell = row.createCell(6);
        cell.setCellValue("PRODUCT_NAME");
        cell = row.createCell(7);
        cell.setCellValue("SPEED");
        cell = row.createCell(8);
        cell.setCellValue("START_DATE");
        cell = row.createCell(9);
        cell.setCellValue("END_DATE");
        cell = row.createCell(10);
        cell.setCellValue("GD_FEE");
        cell = row.createCell(11);
        cell.setCellValue("YY_FEE");
        cell = row.createCell(12);
        cell.setCellValue("NET_FEE");
        cell = row.createCell(13);
        cell.setCellValue("OTHER_FEE");
        cell = row.createCell(14);
        cell.setCellValue("LIMT_TAG");
        cell = row.createCell(15);
        cell.setCellValue("PRICE");

        //第五步，写入数据
        for (int i = 0; i < result.size(); i++) {

            List<String> oneData = result.get(i);
            HSSFRow row1 = sheet.createRow(i + 1);
            for (int j = 0; j < oneData.size(); j++) {

                //创建单元格设值
                row1.createCell(j).setCellValue(oneData.get(j));
            }
        }

        //将文件保存到指定的位置
        try {

            FileOutputStream fos = new FileOutputStream("C:\\Users\\74095\\Desktop\\新建文件夹 (6)\\test2\\北京.xls");
            workbook.write(fos);
            System.out.println("写入成功");
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 创建新excel.
     * @param fileDir  excel的路径
     * @param sheetName 要创建的表格索引
     * @param titleRow excel的第一行即表格头
     */
    public static void createExcel(String fileDir,String sheetName,String titleRow[]) throws Exception{
        //创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
        HSSFSheet sheet1 = workbook.createSheet(sheetName);
        //新建文件
        FileOutputStream out = null;
        try {

            out = new FileOutputStream(fileDir);
            workbook.write(out);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
